package com.example.demo.service.impl;

import com.example.demo.bean.BuildBaseSql;
import com.example.demo.bean.BuildNewHisSql;
import com.example.demo.bean.BuildOldHisSql;
import com.example.demo.core.entity.FailModel;
import com.example.demo.core.enums.ElasticTypeEnum;
import com.example.demo.service.DatabaseService;
import com.example.demo.service.ElasticBulkService;


import org.apache.commons.lang.NullArgumentException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.scheduling.annotation.Async;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import java.sql.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

@Service
public class DatabaseServiceImpl implements DatabaseService {
    private static Logger logger = LoggerFactory.getLogger(DatabaseServiceImpl.class);
    private static Connection libAConnction;

    private final static int batchSize = 10000;
    private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");

    PreparedStatement ps = null;
    ResultSet rs = null;

    @Resource
    private ElasticBulkService elasticBulkService;

    private void getConn(){
        try {
            if(libAConnction == null || libAConnction.isClosed()){
                Class.forName("org.postgresql.Driver");
                String userName = "dhcc";
                String password = "Dhcc@123!";
                libAConnction = DriverManager.getConnection("jdbc:postgresql://192.178.62.103:25308/base_db002?useSSL=false&serverTimezone=UTC",
                        userName, password);
            }
        } catch (ClassNotFoundException e){
            e.printStackTrace();
        }catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Async
    @Override
    public void bulkBySql(String theme, String startTemp, String endTemp, int limit, boolean isNew) throws ParseException {
        Calendar cal = Calendar.getInstance();
        cal.setTime(sdf.parse(startTemp));
        try {
            for (long d = cal.getTimeInMillis(); d <= sdf.parse(endTemp).getTime(); d = BuildBaseSql.get_D_Plaus_1(cal)) {
                FailModel failer = new FailModel();
                //打印年月日
                System.out.println(sdf.format(d));
                String curTemp = sdf.format(d);
                logger.info("================= start query [" + curTemp+"] theme: ["  + theme +"] data");
                long st = System.currentTimeMillis();
                String sql = isNew ? BuildNewHisSql.getSql(theme, curTemp, limit)
                        : BuildOldHisSql.getSql(theme, curTemp, limit);

                long total = dbQuery(theme, sql, failer);

                long et = System.currentTimeMillis();
                logger.info("================= export [" + curTemp+"] theme: ["  + theme +"] data finished。tool: "
                        +(et-st) + "ms. data total:" + total);

                if(failer.isHasFail()){
                    logger.error("export error. message: " + failer.getMessage());
                    break;
                }
            }
        } catch (Exception e){
            e.printStackTrace();
        } finally {
            try {
                if (null != rs) {
                    rs.close();
                }
                if (null != ps) {
                    ps.close();
                }
                if (libAConnction != null) {
                    libAConnction.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                logger.info("SQL ERROR:" + e.getMessage());
            }
        }

    }


//    public void bulkAll(String startTemp, String endTemp, int limit, boolean isNew) throws ParseException {
//        // 导入病人
//        bulkBySql(ElasticTypeEnum.PATIENT.getTheme(), startTemp, endTemp, limit, isNew);
//
//    }

    private long dbQuery(String theme, String sql, FailModel failer) {
        getConn();
        if (libAConnction == null) {
            logger.error("libAConnction is null");
            return 0;
        }

        if (StringUtils.isEmpty(sql)) {
            logger.error("sql is null");
            return 0;
        }

        long totalCount = 0;
        long starttime = System.currentTimeMillis();
        try {
            if (StringUtils.isEmpty(sql)) {
                return 0;
            }

            logger.info("开始抽取[{}],start datetime:[{}], sql:[{}]", theme, new java.util.Date(), sql);
            libAConnction.setAutoCommit(false);
            ps = libAConnction.prepareStatement(sql);
            ps.setFetchSize(batchSize); //每次获取10000条记录
            rs = ps.executeQuery();
            int i = 0;
            long s = System.currentTimeMillis();
            List<Map<String, Object>> dataList = new ArrayList<>();
            while (rs.next()) {
                totalCount++;
                i++;
                Map<String, Object> rowDataMap = getResultMap(rs);
                dataList.add(rowDataMap);
                long e = System.currentTimeMillis();
                if (i >= batchSize) {   // total
                    elasticBulkService.bulk(theme, dataList);

                    logger.info("查询[{}]条数据, time tool:[{}]]ms", dataList.size(), (e - s));
                    s = System.currentTimeMillis();
                    i = 0;
                    dataList.clear();
                }
            }
            if (i > 0) {
                long e = System.currentTimeMillis();
                logger.info("查询[{}]条数据, time tool:[{}]]ms", dataList.size(), (e - s));
                if (dataList.size() > 0) {
                    elasticBulkService.bulk(theme, dataList);
                    dataList.clear();
                }
            }

        } catch (SQLException e) {
            failer.setHasFail(true);
            failer.setMessage(e.getMessage());
            failer.setException(e);
            logger.info("SQL ERROR:" + e.getMessage());
        }

        long endtime = System.currentTimeMillis();
        logger.info("结束抽取[{}],end datetime:[{}], tool:[{}]", theme, (endtime - starttime), totalCount);

        return totalCount;
    }

    private static Map<String, Object> getResultMap(ResultSet rs)
            throws SQLException {
        HashMap<String, Object> result = new HashMap<String, Object>();
        ResultSetMetaData rsmd = rs.getMetaData();
        int count = rsmd.getColumnCount();
        for (int i = 1; i <= count; i++) {
            String key = rsmd.getColumnLabel(i);
            String value = rs.getString(i);
            result.put(key, value);
        }
        return result;
    }





}
